/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package net.hnilica.cisteniulic.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Query;
import net.hnilica.cisteniulic.dto.Ulice;

/**
 *
 * @author swosh
 */
public class UliceDAO {

    private static final String defaultPartMunicipality = "BUBENEČ (PRAHA 7)";
    private static final int limit = 10;
    private static EntityManagerFactory emf = Emf.getInstance();
    private Logger log = Logger.getLogger(getClass().getName());

    public Ulice getUlice(String nazUlice) {
        EntityManager em = emf.createEntityManager();
        try {
            Ulice ulice = em.find(Ulice.class, nazUlice);
            return ulice;
        } catch (Exception e) {
            log.log(Level.WARNING, e.toString());
            return new Ulice();
        } finally {
            em.close();
        }
    }

    public Collection<Ulice> getAllUlice() {
        EntityManager em = emf.createEntityManager();
        try {
            Collection<Ulice> listUlice = new ArrayList<Ulice>();
            String queryString = "SELECT * FROM ulice";
            Query query = em.createNativeQuery(queryString, Ulice.class);
            listUlice = (Collection<Ulice>) query.getResultList();
            return listUlice;
        } catch (Exception e) {
            log.log(Level.WARNING, e.toString());
            return (Collection<Ulice>) new ArrayList<Ulice>();
        } finally {
            em.close();
        }
    }

    public Collection<Ulice> getWatchedUlice(String email) {
        EntityManager em = emf.createEntityManager();
        try {
            Collection<Ulice> listUlice = new ArrayList<Ulice>();
            String queryString = "SELECT * FROM ulice u JOIN info i "
                    + " on u.naz_ulice = i.naz_ulice WHERE i.email = :email";
            Query query = em.createNativeQuery(queryString, Ulice.class);
            query.setParameter("email", email);
            listUlice = (Collection<Ulice>) query.getResultList();
            return listUlice;
        } catch (Exception e) {
            log.log(Level.WARNING, e.toString());
            return (Collection<Ulice>) new ArrayList<Ulice>();
        } finally {
            em.close();
        }
    }

    public Collection<Ulice> getNotWatchedUlice(String email, String partMunicipality) {
        EntityManager em = emf.createEntityManager();
        if ("".equals(partMunicipality) || partMunicipality == null) {
            partMunicipality = defaultPartMunicipality;
        }
        try {
            Collection<Ulice> listUlice = new ArrayList<Ulice>();
            String queryString = "SELECT naz_ulice, naz_casti_obce, naz_obce FROM ulice WHERE "
                    + "naz_casti_obce = :partMunicipality "
                    + "EXCEPT SELECT u.naz_ulice, naz_casti_obce, naz_obce FROM ulice u "
                    + "JOIN info i on i.naz_ulice = u.naz_ulice  "
                    + "WHERE email = :email "
                    + "ORDER BY naz_ulice ASC";
            Query query = em.createNativeQuery(queryString, Ulice.class);
            query.setParameter("email", email);
            query.setParameter("partMunicipality", partMunicipality);
            listUlice = (Collection<Ulice>) query.getResultList();
            return listUlice;
        } catch (Exception e) {
            log.log(Level.WARNING, e.toString());
            return (Collection<Ulice>) new ArrayList<Ulice>();
        } finally {
            em.close();
        }
    }

    public Collection<Ulice> getPartMunOfOneMun(String municipality) {
        EntityManager em = emf.createEntityManager();
        try {
            Collection<Ulice> listUlice = new ArrayList<Ulice>();
            String queryString = "SELECT naz_casti_obce FROM cast_obce "
                + " where naz_obce = :municipality  ORDER BY naz_casti_obce ASC";
            Query query = em.createNativeQuery(queryString);
            query.setParameter("municipality", municipality);
            List list = query.getResultList();
            for (Iterator it = list.iterator(); it.hasNext();) {
                Object object = it.next();
                listUlice.add(new Ulice(null, (String) object, null));

            }
            return listUlice;
        } catch (Exception e) {
            log.log(Level.WARNING, e.toString());
            return (Collection<Ulice>) new ArrayList<Ulice>();
        } finally {
            em.close();
        }
    }

    public Collection<Ulice> getBySearch(String email, String search) {
        EntityManager em = emf.createEntityManager();
            if ("".equals(search) || search == null) {
            search = ".";
        }
        try {
            Collection<Ulice> listUlice = new ArrayList<Ulice>();
            String queryString = "SELECT naz_ulice, naz_casti_obce, naz_obce FROM ulice "
                    + "WHERE naz_ulice ~* :search "
                    + "EXCEPT SELECT u.naz_ulice, naz_casti_obce, naz_obce FROM ulice u "
                    + "JOIN info i on i.naz_ulice = u.naz_ulice  "
                    + "WHERE email = :email "
                    + "ORDER BY naz_ulice ASC LIMIT " + limit;
            Query query = em.createNativeQuery(queryString, Ulice.class);
            query.setParameter("search", search);
            query.setParameter("email", email);
            listUlice = (Collection<Ulice>) query.getResultList();
            return listUlice;
        } catch (Exception e) {
            log.log(Level.WARNING, e.toString());
            return (Collection<Ulice>) new ArrayList<Ulice>();
        } finally {
            em.close();
        }

    }


}
